﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SQLite;
using System.IO;
using System.Data.Common;

/*
 * autor: Ana Isabel Marques 6052
 * version: 25 de Fevereiro de 2012
 */
namespace LP_Csharp
{
    /*
     * Esta classe é destinada às estatísticas.
     */
    class Statistics
    {
        int totalFicheiros = 11;
        List<string> listaEstabelecimentos;
        List<string> listaCategoriasProfissionais;
        List<string[]> listaDocentes;
        List<string> listaTemporaria;

        /*
         * @return listaEstabelecimentos - lista de estabelecimentos
         */ 
        public List<string> getListaEstabelecimentos()
        {
            return listaEstabelecimentos;
        }

        /*
        * @return listaCategoriasProfissionais - lista de categorias profissionais
        */ 
        public List<string> getListaCategoriasProfissionais()
        {
            return listaCategoriasProfissionais;
        }

        /*
        * @return listaDocentes - lista de docentes
        */
        public List<string[]> getListaDocentes()
        {
            return listaDocentes;
        }

        /**
         * @return listaTemporaria - lista temporária
         */
        public List<string> getListaTemporaria()
        {
            return listaTemporaria;
        }

        /**
         * Este método obtém todas as categorias profissionais existentes na base de dados.
         * @return listaCategoriasProfissionais - lista com todas as categorias profissionais existentes na base de dados.
         */
        public List<string> obterCategoriasProfissionais()
        {

            SQLiteConnection connection = new SQLiteConnection("Data Source = rebides.db"); //Cria uma conexão à base de dados 'rebides.db'
            connection.Open();//Abre a conexão

            SQLiteTransaction transaction = connection.BeginTransaction();//Inicia uma transacção para melhorar o desempenho de execução.

            listaTemporaria = new List<string>();
            listaCategoriasProfissionais = new List<string>();


            //Percorre todas as as tabelas da base de dados
            for (int numFicheiro = 0; numFicheiro < totalFicheiros; numFicheiro++)
            {
                SQLiteCommand command = connection.CreateCommand();//Cria um comando
                command.CommandText = String.Format("SELECT * FROM reb{0}", numFicheiro);//Selecciona todo o conteúdo da tabela 
                DbDataReader reader = command.ExecuteReader();//Inicializa o leitor da base de dados

                //Enquanto existirem linhas para ler 
                while (reader.Read())
                {
                    listaTemporaria.Add((string)reader[4]); //adiciona à lista temporária, as categorias profissionais existentes
                }
            }

            transaction.Commit();  //Confirma a transacção
            connection.Close();   //Fecha a conexão

            listaTemporaria.Sort(); // Ordena os elementos da lista temporária

            //Guarda na variável totalCategorias todas as categorias profissionais diferentes existentes na lista temporária.
            var totalCategorias = (from categoria in listaTemporaria
                                   select categoria).Distinct();

            //Adiciona à listaCategoriasProfissionais, cada elemento existente em totalCategorias
            foreach (string categoria in totalCategorias)
            {
                listaCategoriasProfissionais.Add(categoria);
            }

            return listaCategoriasProfissionais;
        }

        /**
         * Este método obtém todos os estabelecimentos de ensino existentes na base de dados.
         * @return listaEstabelecimentosEnsino - lista com todos os estabelecimentos de ensino existentes na base de dados
         */
        public List<string> obterEstabelecimentosEnsino()
        {
            SQLiteConnection connection = new SQLiteConnection("Data Source = rebides.db"); //Cria uma conexão à base de dados 'rebides.db'
            connection.Open();//Abre a conexão

            SQLiteTransaction transaction = connection.BeginTransaction();//Inicia uma transacção para melhorar o desempenho de execução.

            listaTemporaria = new List<string>();
            listaEstabelecimentos = new List<string>();


            //Percorre todas as tabelas da base de dados
            for (int numFicheiro = 0; numFicheiro < totalFicheiros; numFicheiro++)
            {
                SQLiteCommand command = connection.CreateCommand();//Cria um comando
                command.CommandText = String.Format("SELECT * FROM reb{0}", numFicheiro); //Selecciona todo o conteúdo da tabela 
                DbDataReader reader = command.ExecuteReader(); //Inicializa o leitor da base de dados

                //Enquanto existir linhas para ler
                while (reader.Read())
                {
                    listaTemporaria.Add((string)reader[8]); //Adiciona à lista temporária, os nomes dos estabelecimentos de ensino existentes na 
                }
            }

            transaction.Commit(); //Confirma a transacção
            connection.Close(); // Fecha a conexão

            listaTemporaria.Sort(); //Ordena os elementos da lista temporária

            //Guarda na variável totalEstabelecimentos todos os estabelecimentos de ensino diferentes existentes~na lista temporária
            var totalEstabelecimentos = (from estabelecimento in listaTemporaria
                                         select estabelecimento).Distinct();

            //Adiciona à listaEstabelecimentos, cada elemento existente em totalEstabelecimentos
            foreach (string estabelecimento in totalEstabelecimentos)
            {
                listaEstabelecimentos.Add(estabelecimento);
            }

            return listaEstabelecimentos;
        }

        /* Este método corresponde à primeira estatística.
         * Devolve o número total de estabelecimentos existentes em cada ano.
         * @param ano - ano
         * @return totalEstabelecimentos - número total de estabelecimentos existentes num determinado ano.
         */
        public int numeroTotalEstabelecimentosAno(int ano)
        {

            SQLiteConnection connection = new SQLiteConnection("Data Source = rebides.db"); //Cria uma conexão à base de dados 'rebides.db'
            connection.Open();//Abre a conexão

            SQLiteTransaction transaction = connection.BeginTransaction();//Inicia uma transacção para melhorar o desempenho de execução.

            SQLiteCommand command = connection.CreateCommand();//Cria um comando

            listaEstabelecimentos = new List<string>();

            int a = ano - 2000;
               
            command.CommandText = String.Format("SELECT * FROM reb{0}", a); //Selecciona todo o conteúdo existente na tabela correspondente ao ano

            DbDataReader reader = command.ExecuteReader(); //Inicializa o leitor da base de dados.

            //Enquanto existir linhas para ler
            while (reader.Read())
            {
                listaEstabelecimentos.Add((string)reader[8]); //Adiciona a listaEstabelecimentos, os nomes de estabelecimentos de ensino existentes na tabela
            }

            //Guarda na variável totalEstabelecimentos, o número total de estabelecimentos diferentes existentes na listaEstabelecimentos.
            var totalEstabelecimentos = ((from numeroEstabelecimentos in listaEstabelecimentos
                                    select numeroEstabelecimentos).Distinct()).Count();

            transaction.Commit(); //Confirma a transacção
            connection.Close(); //Fecha a conexão

            return totalEstabelecimentos;
        }

        /**
         * Este método corresponde à segunda estatística.
         * Devolve o número total de docentes existentes por categoria profissional em cada ano. 
         * @param ano - ano
         * @param categoria - nome da categoria profissional
         * @param estabelecimento - nome do estabelecimento de ensino.
         * @return totalDocentes - número total de docentes existentes numa determinada categoria profissional e num determinado ano.
         */
        public int distribuicaoDocentesCategoriaProfissionalAno(int ano, string categoria)
        {
            SQLiteConnection connection = new SQLiteConnection("Data Source = rebides.db"); //Cria uma conexão à base de dados 'rebides.db'
            connection.Open();//Abre a conexão

            SQLiteTransaction transaction = connection.BeginTransaction();//Inicia uma transacção para melhorar o desempenho de execução.

            SQLiteCommand command = connection.CreateCommand();//Cria um comando

            List<string> lista = new List<string>();

            int a = ano - 2000;
            command.CommandText = String.Format("SELECT * FROM reb{0}", a); // Selecciona todo o conteúdo existente na tabela correspondente ao ano.
            
            DbDataReader reader = command.ExecuteReader(); // Inicializa o leitor da base de dados

            List<string[]> listaInformacao = new List<string[]>();

            //Enquanto existir linhas para ler
            while (reader.Read())
            {
                string[] information = { (string)reader[1], (string)reader[4] };
                listaInformacao.Add(information); // guarda na listaInformacao, o nome do docente e a categoria profissional correspondente.
            }

            //Guarda na variável totalDocentes, o número total de docentes diferentes existentes na listaInformacao com a categoria profissional igual à categoria escolhida pelo utilizador (passada como parâmetro)
            var totalDocentes = ((from docente in listaInformacao
                                  where docente.Contains(categoria)
                                  select docente).Distinct().Count());

            transaction.Commit(); //Confirma a transacção
            connection.Close(); // Fecha a transacção.
            

            return totalDocentes; 
        }

        /*
         * Este método corresponde à terceira estatística.
         * Devolve o número total de docentes existentes num determinado estabelecimento de ensino e com determinada categoria profissional
         * @param categoria - nome da categoria profissional
         * @param estabelecimento - nome do estabelecimento de ensino
         * @return totalDocentes - número total de docentes existentes 
         */
        public int numeroDocentesCategoriaProfissionalEstabelecimento(string categoria, string estabelecimento)
        {
            SQLiteConnection connection = new SQLiteConnection("Data Source = rebides.db"); //Cria uma conexão à base de dados 'rebides.db'
            connection.Open();//Abre a conexão

            SQLiteTransaction transaction = connection.BeginTransaction();//Inicia uma transacção para melhorar o desempenho de execução.
            List<string[]> listaInformacao = new List<string[]>();

            for (int numFicheiro = 0; numFicheiro < totalFicheiros; numFicheiro++)
            {
                SQLiteCommand command = connection.CreateCommand();//Cria um comando
                command.CommandText = String.Format("SELECT * FROM reb{0}", numFicheiro); //Selecciona todo o conteúdo da tabela 
                DbDataReader reader = command.ExecuteReader(); //Inicializa o leitor da base de dados.

                //Enquanto existir linhas para ler
                while (reader.Read())
                {
                    string[] information = { (string)reader[1], (string)reader[4], (string)reader[8] };
                    listaInformacao.Add(information); //Adiciona à listaInformacao, o nome do docente, a categoria profissional e o nome do estabelecimento de ensino correspondente.
                }
            }
            
            transaction.Commit(); //Confirma a transacção
            connection.Close(); //Fecha a conexão

            //Guarda na variável totalDocentes, o número total de docentes diferentes existentes na listaInformacao com a categoria profissional igual à categoria escolhida pelo utilizador (passada como parâmetro) e o nome de estabelecimento de ensino igual ao estabelecimento de ensino escolhido pelo utilizador (passado como parâmetro).
            var totalDocentes = ((from docente in listaInformacao
                                      where docente.Contains(categoria) && docente.Contains(estabelecimento)
                                      select docente).Distinct().Count());
            return totalDocentes;
        }

        /*
        * Este método corresponde à terceira estatística.
        * Devolve uma lista com todos os docentes existentes num determinado estabelecimento de ensino e com determinada categoria profissional
        * @param categoria - nome da categoria profissional
        * @param estabelecimento - nome do estabelecimento de ensino
        * @return listaDocentes - lista com todos os docentes com determinada categoria profissional e com determinado estabelecimento de ensino.  
        */
        public List<string[]> listaDocentesCategoriaProfissionalEstabelecimento(string categoria, string estabelecimento)
        {
            SQLiteConnection connection = new SQLiteConnection("Data Source = rebides.db"); //Cria uma conexão à base de dados 'rebides.db'
            connection.Open();//Abre a conexão

            SQLiteTransaction transaction = connection.BeginTransaction();//Inicia uma transacção para melhorar o desempenho de execução.
            
            List<string[]> listaInformacao = new List<string[]>();

            listaDocentes = new List<string[]>();

            //Percorre todas as tabelas existentes na base de dados.
            for (int numFicheiro = 0; numFicheiro < totalFicheiros; numFicheiro++)
            {
                SQLiteCommand command = connection.CreateCommand();//Cria um comando
                command.CommandText = String.Format("SELECT * FROM reb{0}", numFicheiro); // Selecciona todo o conteúdo da tabela
                DbDataReader reader = command.ExecuteReader();  //Inicializa o leitor da base de dados

                //Enquanto existir linhas para ler
                while (reader.Read())
                {
                    string[] information = { (string)reader[1], (string)reader[4], (string)reader[8] };
                    listaInformacao.Add(information); //Adiciona à listaInformacao, o nome do docente, a categoria profissional e o nome do estabelecimento de ensino.
                }
            }

            transaction.Commit(); //Confirma a transacção
            connection.Close(); //Fecha a transacção.

            //Guarda na variável docentes, todos os docentes diferentes existentes, com a categoria profissional igual à categoria profissional escolhida pelo utilizador (passada como parâmetro) e com o nome do estabelecimento de ensino igual ao estabelecimento de ensino escolhido pelo utilizador (passado por parâmetro). 
            var docentes = (from docente in listaInformacao
                                  where docente.Contains(categoria) && docente.Contains(estabelecimento)
                                  select docente).Distinct();

            //Adiciona à listaDocentes, cada elemento existente em docentes
            foreach (string[] d in docentes)
            {
                listaDocentes.Add(d);
            }

            return listaDocentes;
        }
    }
}